package iot.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import iot.bean.Tgatedevice;
import iot.utils.ConnDb;

public class UtilsDao {
	private Connection cn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public void closedb() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 存在重复代码可复用
	public Map<Long, String> relDeviceTypeAll() {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tdevicetype";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("devicetypename"));
				rel.put(rs.getLong("id"), rs.getString("devicetypename"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relUserAll() {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tuser";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("username"));
				rel.put(rs.getLong("id"), rs.getString("username"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relProjectAll() {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tproject";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("projectname"));
				rel.put(rs.getLong("id"), rs.getString("projectname"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relDeviceAll() {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tdevice";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("devicename"));
				rel.put(rs.getLong("id"), rs.getString("devicename"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relGateAll() {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tgate";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("gatename"));
				rel.put(rs.getLong("id"), rs.getString("gatename"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	// Need public method to use "UserName"->"UID" to 'select * from tproject
	// where uid="UID"'
	public Map<Long, String> relProjectByUid(Long UID) {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tproject where uid='" + UID + "'";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("projectname"));
				rel.put(rs.getLong("id"), rs.getString("projectname"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relGateByPid(Long PID) {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tgate where pid='" + PID + "'";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("gatename"));
				rel.put(rs.getLong("id"), rs.getString("gatename"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Map<Long, String> relGateByUid(Long UID) {
		Map<Long, String> rel = new HashMap<Long, String>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tgate where pid=ANY(select id from tproject where uid="
				+ UID + ")";// error
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getLong("id") + ": "
						+ rs.getString("gatename"));
				rel.put(rs.getLong("id"), rs.getString("gatename"));
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return rel;
	}

	public Long getUidByUserName(String UserName) {
		Long uid = 0L;
		for (Map.Entry<Long, String> entry : relUserAll().entrySet()) {
			if (entry.getValue().equals(UserName))
				uid = entry.getKey();
		}
		return uid;
	}

	public Long getUidByPid(Long PID) {
		Long uid = 0L;
		cn = new ConnDb().getcon();
		String sqlstr = "select uid from tproject where id='" + PID + "'";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(PID + ": " + rs.getLong("uid"));
				uid = rs.getLong("uid");
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return uid;
	}

	public Long getUidByGid(Long GID) {
		Long uid = 0L;
		cn = new ConnDb().getcon();
		String sqlstr = "select uid from tproject where id=(select DISTINCT pid from tgate where id="
				+ GID + ")";
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(GID + ": " + rs.getLong("uid"));
				uid = rs.getLong("uid");
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return uid;
	}

	public List<Tgatedevice> getGateDevicesByGid(Long GID) {
		List<Tgatedevice> retlist = new ArrayList<Tgatedevice>();
		cn = new ConnDb().getcon();
		String sqlstr = "select * from tgatedevice where gid=" + GID;
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				Tgatedevice temp = new Tgatedevice();
				System.out.println(GID + ": "
						+ rs.getString("clientdevicename") + "- "
						+ rs.getString("clientdeviceid") + "- "
						+ rs.getLong("id") + "- " + rs.getLong("did"));
				temp.setId(rs.getLong("id"));
				temp.setDid(rs.getLong("did"));
				temp.setClientdevicename(rs.getString("clientdevicename"));
				temp.setClientdeviceid(rs.getString("clientdeviceid"));
				retlist.add(temp);
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return retlist;
	}

	public String getDeviceTypeNameByDid(Long DID) {
		String ret = "";
		cn = new ConnDb().getcon();
		String sqlstr = "select devicetypename from tdevicetype where id=(select dtid from tdevice where id="+ DID +")" ;
		System.out.println(sqlstr);
		try {
			ps = cn.prepareStatement(sqlstr);

			rs = ps.executeQuery();
			while (rs.next()) {
				ret = rs.getString("devicetypename");
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return ret;
	}
}
